/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2008                    */
/* Created on:     2011/8/29 21:32:38                           */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('AdClickInfo') and o.name = 'FK_10')
alter table AdClickInfo
   drop constraint FK_10
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('AdClickInfo') and o.name = 'FK_8')
alter table AdClickInfo
   drop constraint FK_8
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('AdvertisementInfo') and o.name = 'FK_2')
alter table AdvertisementInfo
   drop constraint FK_2
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BatchInfo') and o.name = 'FK_5')
alter table BatchInfo
   drop constraint FK_5
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('BatchInfo') and o.name = 'FK_9')
alter table BatchInfo
   drop constraint FK_9
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('RegisterInfo') and o.name = 'FK_7')
alter table RegisterInfo
   drop constraint FK_7
go

if exists (select 1
            from  sysobjects
           where  id = object_id('AdClickTotalView')
            and   type = 'V')
   drop view AdClickTotalView
go

if exists (select 1
            from  sysobjects
           where  id = object_id('AdManageView')
            and   type = 'V')
   drop view AdManageView
go

if exists (select 1
            from  sysobjects
           where  id = object_id('AdvertisementInfoView')
            and   type = 'V')
   drop view AdvertisementInfoView
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BatchInfoView')
            and   type = 'V')
   drop view BatchInfoView
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CurrentAdClickTotalView')
            and   type = 'V')
   drop view CurrentAdClickTotalView
go

if exists (select 1
            from  sysobjects
           where  id = object_id('CurrentAdThirtyDaysInsClickTotalView')
            and   type = 'V')
   drop view CurrentAdThirtyDaysInsClickTotalView
go

if exists (select 1
            from  sysobjects
           where  id = object_id('InfoSummary')
            and   type = 'V')
   drop view InfoSummary
go

if exists (select 1
            from  sysobjects
           where  id = object_id('RegisterInfoView')
            and   type = 'V')
   drop view RegisterInfoView
go

if exists (select 1
            from  sysobjects
           where  id = object_id('RegisterManageInfoView')
            and   type = 'V')
   drop view RegisterManageInfoView
go

if exists (select 1
            from  sysobjects
           where  id = object_id('RegisterTotalView')
            and   type = 'V')
   drop view RegisterTotalView
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ThirtyDaysInsClickTotalView')
            and   type = 'V')
   drop view ThirtyDaysInsClickTotalView
go

if exists (select 1
            from  sysobjects
           where  id = object_id('ThirtyDaysInsRegisterTotalView')
            and   type = 'V')
   drop view ThirtyDaysInsRegisterTotalView
go

if exists (select 1
            from  sysobjects
           where  id = object_id('AdClickInfo')
            and   type = 'U')
   drop table AdClickInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('AdvertisementInfo')
            and   type = 'U')
   drop table AdvertisementInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BatchInfo')
            and   type = 'U')
   drop table BatchInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('BusinessInfo')
            and   type = 'U')
   drop table BusinessInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('FactoryInfo')
            and   type = 'U')
   drop table FactoryInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('RegisterInfo')
            and   type = 'U')
   drop table RegisterInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('SMSInfo')
            and   type = 'U')
   drop table SMSInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserInfo')
            and   type = 'U')
   drop table UserInfo
go

/*==============================================================*/
/* Table: AdClickInfo                                           */
/*==============================================================*/
create table AdClickInfo (
   ID                   int                  identity(10000,1) not for replication,
   AdID                 int                  null,
   BatchID              int                  null,
   ClickDateTime        datetime             null,
   IMSI                 varchar(255)         null,
   InfoCenterNo         varchar(255)         null,
   NumberType           varchar(255)         null,
   constraint PK_ADCLICKINFO primary key (ID)
)
go

/*==============================================================*/
/* Table: AdvertisementInfo                                     */
/*==============================================================*/
create table AdvertisementInfo (
   ID                   int                  identity(10000,1) not for replication,
   BatchID              int                  null,
   ProductName          varchar(255)         null,
   ProductCode          varchar(255)         null,
   Type                 varchar(255)         null,
   Introduction         varchar(1000)        null,
   Inventory            int                  null,
   ServiceTel           varchar(255)         null,
   ComplaintTel         varchar(255)         null,
   ConsultingTel        varchar(255)         null,
   PlaceOfOrigin        varchar(255)         null,
   PeriodOfBuy          datetime             null,
   ConsumptionOfPeriod  datetime             null,
   Content              varchar(2000)        null,
   ImgAddress           varchar(255)         null,
   AddDatetime          datetime             null,
   MaturityDatetime     datetime             null,
   IsDisplay            bit                  null,
   constraint PK_ADVERTISEMENTINFO primary key (ID)
)
go

/*==============================================================*/
/* Table: BatchInfo                                             */
/*==============================================================*/
create table BatchInfo (
   ID                   int                  identity(10000,1) not for replication,
   BusinessID           int                  null,
   FactoryID            int                  null,
   BatchCode            int                  null,
   Model                varchar(255)         null,
   constraint PK_BATCHINFO primary key (ID)
)
go

/*==============================================================*/
/* Table: BusinessInfo                                          */
/*==============================================================*/
create table BusinessInfo (
   ID                   int                  identity(10000,1) not for replication,
   UserName             varchar(255)         null,
   Password             varchar(255)         null,
   Name                 varchar(255)         null,
   Phone                varchar(255)         null,
   Email                varchar(255)         null,
   QQ                   varchar(255)         null,
   IsActive             bit                  null,
   constraint PK_BUSINESSINFO primary key (ID)
)
go

/*==============================================================*/
/* Table: FactoryInfo                                           */
/*==============================================================*/
create table FactoryInfo (
   ID                   int                  identity(10000,1) not for replication,
   UserName             varchar(255)         null,
   Password             varchar(255)         null,
   Name                 varchar(255)         null,
   Phone                varchar(255)         null,
   Email                varchar(255)         null,
   QQ                   varchar(255)         null,
   IsActive             bit                  null,
   constraint PK_FACTORYINFO primary key (ID)
)
go

/*==============================================================*/
/* Table: RegisterInfo                                          */
/*==============================================================*/
create table RegisterInfo (
   ID                   int                  identity(10000,1) not for replication,
   BatchID              int                  null,
   PhoneNo              varchar(255)         null,
   InfoCenterNo         varchar(255)         null,
   IMSI                 varchar(255)         null,
   NumberType           varchar(255)         null,
   RegisterDatetime     datetime             null,
   constraint PK_REGISTERINFO primary key (ID)
)
go

/*==============================================================*/
/* Table: SMSInfo                                               */
/*==============================================================*/
create table SMSInfo (
   ID                   int                  identity(10000,1) not for replication,
   PhoneNo              varchar(255)         null,
   Content              varchar(1000)        null,
   SendDateTime         datetime             null,
   Status               varchar(255)         null,
   constraint PK_SMSINFO primary key (ID)
)
go

/*==============================================================*/
/* Table: UserInfo                                              */
/*==============================================================*/
create table UserInfo (
   ID                   int                  identity(10000,1) not for replication,
   UserName             varchar(255)         null,
   Password             varchar(255)         null,
   Name                 varchar(255)         null,
   Phone                varchar(255)         null,
   Email                varchar(255)         null,
   QQ                   varchar(255)         null,
   IsActive             bit                  null,
   Type                 varchar(255)         null,
   constraint PK_USERINFO primary key (ID)
)
go

/*==============================================================*/
/* View: AdClickTotalView                                       */
/*==============================================================*/
create view AdClickTotalView as
SELECT     dbo.BatchInfo.ID, COUNT(dbo.AdClickInfo.ID) AS AdClickTotal
FROM         dbo.BatchInfo LEFT OUTER JOIN
                      dbo.AdClickInfo ON dbo.AdClickInfo.BatchID = dbo.BatchInfo.ID
GROUP BY dbo.BatchInfo.ID
go

/*==============================================================*/
/* View: CurrentAdClickTotalView                                */
/*==============================================================*/
create view CurrentAdClickTotalView as
SELECT     dbo.AdvertisementInfo.ID, COUNT(dbo.AdClickInfo.ID) AS AdClickTotal
FROM         dbo.AdvertisementInfo LEFT OUTER JOIN
                      dbo.AdClickInfo ON dbo.AdvertisementInfo.ID = dbo.AdClickInfo.AdID
GROUP BY dbo.AdvertisementInfo.ID
go

/*==============================================================*/
/* View: CurrentAdThirtyDaysInsClickTotalView                   */
/*==============================================================*/
create view CurrentAdThirtyDaysInsClickTotalView as
SELECT     dbo.AdvertisementInfo.ID, COUNT(dbo.AdClickInfo.ID) AS ThirtyDaysInsClickTotal
FROM         dbo.AdvertisementInfo LEFT OUTER JOIN
                      dbo.AdClickInfo ON dbo.AdvertisementInfo.ID = dbo.AdClickInfo.AdID
WHERE     (DATEDIFF(day, dbo.AdClickInfo.ClickDateTime, GETDATE()) <= 30)
GROUP BY dbo.AdvertisementInfo.ID
go

/*==============================================================*/
/* View: RegisterTotalView                                      */
/*==============================================================*/
create view RegisterTotalView as
SELECT     dbo.BatchInfo.ID, COUNT(dbo.RegisterInfo.ID) AS RegisterTotal
FROM         dbo.BatchInfo LEFT OUTER JOIN
                      dbo.RegisterInfo ON dbo.BatchInfo.ID = dbo.RegisterInfo.BatchID
GROUP BY dbo.BatchInfo.ID
go

/*==============================================================*/
/* View: ThirtyDaysInsClickTotalView                            */
/*==============================================================*/
create view ThirtyDaysInsClickTotalView as
SELECT     dbo.BatchInfo.ID, COUNT(dbo.AdClickInfo.ID) AS ThirtyDaysInsClickTotal
FROM         dbo.BatchInfo LEFT OUTER JOIN
                      dbo.AdClickInfo ON dbo.AdClickInfo.BatchID = dbo.BatchInfo.ID
WHERE     (DATEDIFF(day, dbo.AdClickInfo.ClickDateTime, GETDATE()) <= 30)
GROUP BY dbo.BatchInfo.ID
go

/*==============================================================*/
/* View: ThirtyDaysInsRegisterTotalView                         */
/*==============================================================*/
create view ThirtyDaysInsRegisterTotalView as
SELECT     dbo.BatchInfo.ID, COUNT(dbo.RegisterInfo.ID) AS ThirtyDaysInsRegisterTotal
FROM         dbo.BatchInfo LEFT OUTER JOIN
                      dbo.RegisterInfo ON dbo.BatchInfo.ID = dbo.RegisterInfo.BatchID
WHERE     (DATEDIFF(day, dbo.RegisterInfo.RegisterDatetime, GETDATE()) <= 30)
GROUP BY dbo.BatchInfo.ID
go

/*==============================================================*/
/* View: AdManageView                                           */
/*==============================================================*/
create view AdManageView as
SELECT     dbo.AdvertisementInfo.ID, dbo.BusinessInfo.ID AS BusinessID, dbo.FactoryInfo.ID AS FactoryID, dbo.BatchInfo.BatchCode, dbo.BusinessInfo.Name AS BusinessName, dbo.FactoryInfo.Name AS FactoryName, dbo.BatchInfo.Model, 
                      dbo.AdvertisementInfo.ProductName, dbo.AdvertisementInfo.ProductCode, dbo.AdvertisementInfo.Type, dbo.AdvertisementInfo.Introduction, 
                      dbo.AdvertisementInfo.Inventory, dbo.AdvertisementInfo.ServiceTel, dbo.AdvertisementInfo.ComplaintTel, dbo.AdvertisementInfo.ConsultingTel, 
                      dbo.AdvertisementInfo.PlaceOfOrigin, dbo.AdvertisementInfo.PeriodOfBuy, dbo.AdvertisementInfo.ConsumptionOfPeriod, dbo.AdvertisementInfo.[Content], 
                      dbo.AdvertisementInfo.ImgAddress, dbo.AdvertisementInfo.AddDatetime, dbo.AdvertisementInfo.MaturityDatetime, dbo.AdvertisementInfo.IsDisplay, 
                      dbo.CurrentAdClickTotalView.AdClickTotal, dbo.CurrentAdThirtyDaysInsClickTotalView.ThirtyDaysInsClickTotal
FROM         dbo.AdvertisementInfo INNER JOIN
                      dbo.BatchInfo ON dbo.AdvertisementInfo.BatchID = dbo.BatchInfo.ID INNER JOIN
                      dbo.BusinessInfo ON dbo.BatchInfo.BusinessID = dbo.BusinessInfo.ID INNER JOIN
                      dbo.FactoryInfo ON dbo.BatchInfo.FactoryID = dbo.FactoryInfo.ID LEFT OUTER JOIN
                      dbo.CurrentAdClickTotalView ON dbo.AdvertisementInfo.ID = dbo.CurrentAdClickTotalView.ID LEFT OUTER JOIN
                      dbo.CurrentAdThirtyDaysInsClickTotalView ON dbo.AdvertisementInfo.ID = dbo.CurrentAdThirtyDaysInsClickTotalView.ID
go

/*==============================================================*/
/* View: AdvertisementInfoView                                  */
/*==============================================================*/
create view AdvertisementInfoView as
SELECT     dbo.BatchInfo.ID, dbo.BusinessInfo.ID AS BusinessID, dbo.FactoryInfo.ID AS FactoryID, dbo.BatchInfo.BatchCode, dbo.BusinessInfo.Name AS BusinessName, dbo.FactoryInfo.Name AS FactoryName, dbo.AdvertisementInfo.ProductName, 
                      dbo.AdvertisementInfo.ProductCode, dbo.AdvertisementInfo.Type, dbo.AdClickTotalView.AdClickTotal, dbo.ThirtyDaysInsClickTotalView.ThirtyDaysInsClickTotal, 
                      dbo.BatchInfo.Model, dbo.AdvertisementInfo.ID AS AdID
FROM         dbo.BatchInfo LEFT OUTER JOIN
                      dbo.BusinessInfo ON dbo.BatchInfo.BusinessID = dbo.BusinessInfo.ID LEFT OUTER JOIN
                      dbo.FactoryInfo ON dbo.BatchInfo.FactoryID = dbo.FactoryInfo.ID LEFT OUTER JOIN
                      dbo.AdvertisementInfo ON dbo.BatchInfo.ID = dbo.AdvertisementInfo.BatchID LEFT OUTER JOIN
                      dbo.AdClickTotalView ON dbo.BatchInfo.ID = dbo.AdClickTotalView.ID LEFT OUTER JOIN
                      dbo.ThirtyDaysInsClickTotalView ON dbo.BatchInfo.ID = dbo.ThirtyDaysInsClickTotalView.ID
go

/*==============================================================*/
/* View: BatchInfoView                                          */
/*==============================================================*/
create view BatchInfoView as
SELECT     dbo.BatchInfo.ID, dbo.BusinessInfo.ID AS BusinessID, dbo.FactoryInfo.ID AS FactoryID, dbo.BatchInfo.BatchCode, dbo.BusinessInfo.Name AS BusinessName, dbo.FactoryInfo.Name AS FactoryName, dbo.BatchInfo.Model
FROM         dbo.BatchInfo INNER JOIN
                      dbo.BusinessInfo ON dbo.BatchInfo.BusinessID = dbo.BusinessInfo.ID INNER JOIN
                      dbo.FactoryInfo ON dbo.BatchInfo.FactoryID = dbo.FactoryInfo.ID
go

/*==============================================================*/
/* View: InfoSummary                                            */
/*==============================================================*/
create view InfoSummary as
SELECT     dbo.BatchInfo.ID, dbo.BusinessInfo.ID AS BusinessID, dbo.FactoryInfo.ID AS FactoryID, dbo.BatchInfo.BatchCode, dbo.BusinessInfo.Name AS BusinessName, dbo.FactoryInfo.Name AS FactoryName, 
                      dbo.AdClickTotalView.AdClickTotal, dbo.ThirtyDaysInsClickTotalView.ThirtyDaysInsClickTotal, dbo.RegisterTotalView.RegisterTotal, 
                      dbo.ThirtyDaysInsRegisterTotalView.ThirtyDaysInsRegisterTotal, dbo.BatchInfo.Model
FROM         dbo.BatchInfo LEFT OUTER JOIN
                      dbo.BusinessInfo ON dbo.BatchInfo.BusinessID = dbo.BusinessInfo.ID LEFT OUTER JOIN
                      dbo.FactoryInfo ON dbo.BatchInfo.FactoryID = dbo.FactoryInfo.ID LEFT OUTER JOIN
                      dbo.AdClickTotalView ON dbo.BatchInfo.ID = dbo.AdClickTotalView.ID LEFT OUTER JOIN
                      dbo.RegisterTotalView ON dbo.BatchInfo.ID = dbo.RegisterTotalView.ID LEFT OUTER JOIN
                      dbo.ThirtyDaysInsClickTotalView ON dbo.BatchInfo.ID = dbo.ThirtyDaysInsClickTotalView.ID LEFT OUTER JOIN
                      dbo.ThirtyDaysInsRegisterTotalView ON dbo.BatchInfo.ID = dbo.ThirtyDaysInsRegisterTotalView.ID
go

/*==============================================================*/
/* View: RegisterInfoView                                       */
/*==============================================================*/
create view RegisterInfoView as
SELECT     dbo.BatchInfo.ID, dbo.BusinessInfo.ID AS BusinessID, dbo.FactoryInfo.ID AS FactoryID, dbo.BatchInfo.BatchCode, dbo.BusinessInfo.Name AS BusinessName, dbo.FactoryInfo.Name AS FactoryName, dbo.RegisterTotalView.RegisterTotal, 
                      dbo.ThirtyDaysInsRegisterTotalView.ThirtyDaysInsRegisterTotal, dbo.BatchInfo.Model
FROM         dbo.BatchInfo LEFT OUTER JOIN
                      dbo.BusinessInfo ON dbo.BatchInfo.BusinessID = dbo.BusinessInfo.ID LEFT OUTER JOIN
                      dbo.FactoryInfo ON dbo.BatchInfo.FactoryID = dbo.FactoryInfo.ID LEFT OUTER JOIN
                      dbo.RegisterTotalView ON dbo.BatchInfo.ID = dbo.RegisterTotalView.ID LEFT OUTER JOIN
                      dbo.ThirtyDaysInsRegisterTotalView ON dbo.BatchInfo.ID = dbo.ThirtyDaysInsRegisterTotalView.ID
go

/*==============================================================*/
/* View: RegisterManageInfoView                                 */
/*==============================================================*/
create view RegisterManageInfoView as
SELECT     dbo.RegisterInfo.ID, dbo.BusinessInfo.ID AS BusinessID, dbo.FactoryInfo.ID AS FactoryID, dbo.BatchInfo.BatchCode, dbo.BatchInfo.Model, 
                      dbo.BusinessInfo.Name AS BusinessName, dbo.FactoryInfo.Name AS FactoryName, dbo.RegisterInfo.PhoneNo, dbo.RegisterInfo.InfoCenterNo, dbo.RegisterInfo.IMSI, 
                      dbo.RegisterInfo.NumberType, dbo.RegisterInfo.RegisterDatetime
FROM         dbo.RegisterInfo INNER JOIN
                      dbo.BatchInfo ON dbo.RegisterInfo.BatchID = dbo.BatchInfo.ID INNER JOIN
                      dbo.BusinessInfo ON dbo.BatchInfo.BusinessID = dbo.BusinessInfo.ID INNER JOIN
                      dbo.FactoryInfo ON dbo.BatchInfo.FactoryID = dbo.FactoryInfo.ID
go

alter table AdClickInfo
   add constraint FK_10 foreign key (BatchID)
      references BatchInfo (ID)
go

alter table AdClickInfo
   add constraint FK_8 foreign key (AdID)
      references AdvertisementInfo (ID)
go

alter table AdvertisementInfo
   add constraint FK_2 foreign key (BatchID)
      references BatchInfo (ID)
go

alter table BatchInfo
   add constraint FK_5 foreign key (BusinessID)
      references BusinessInfo (ID)
go

alter table BatchInfo
   add constraint FK_9 foreign key (FactoryID)
      references FactoryInfo (ID)
go

alter table RegisterInfo
   add constraint FK_7 foreign key (BatchID)
      references BatchInfo (ID)
go

